using Google.GData.Client;
using Google.GData.Extensions;
using Newtonsoft.Json;
using Google.GData.Spreadsheets;
using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using System.Linq;
using System.Xml;

class Script
{
    static public void Main(string[] args)
    {
		var sb = new StringBuilder();
		sb.AppendLine("-- Automatisch aangemaakte file! Niet wijzigen!");
		sb.AppendLine("SET NOCOUNT ON");
		sb.AppendLine("SET QUOTED_IDENTIFIER ON");
		sb.AppendLine("GO");
		sb.AppendLine();
		sb.AppendLine("PRINT '---- CRUD Matrix'");
		sb.AppendLine();
		sb.AppendLine("SET TRANSACTION ISOLATION LEVEL READ COMMITTED");
		sb.AppendLine("GO");
		sb.AppendLine();
		sb.AppendLine("BEGIN TRAN");
		sb.AppendLine();

		var service = new SpreadsheetsService("test");
		service.setUserCredentials(GetConfig("gmailuser"), GetConfig("gmailpass"));
		var feed = service.Query(new SpreadsheetQuery() { Title = "CRUD Matrix" });
		var sheet = feed.Entries.OfType<SpreadsheetEntry>().FirstOrDefault();
		var worksheet = sheet.Worksheets.Entries.OfType<WorksheetEntry>().SingleOrDefault(e => e.Title.Text == "CRUD Matrix");
		var listFeedLink = worksheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);

		var listQuery = new ListQuery(listFeedLink.HRef.ToString());
		var listFeed = service.Query(listQuery);
		string tableName = null;

		var rights = new List<ColumnRight>();

		foreach (var row in listFeed.Entries.OfType<ListEntry>())
		{
			if (row.Title.Text.StartsWith("tbl"))
			{
				tableName = row.Title.Text;
				continue;
			}

			foreach (var element in row.Elements.OfType<ListEntry.Custom>())
			{
				string rolename = GetRoleName(element.LocalName);
				if (rolename == null)
					continue;

				var c = element.Value.Contains("C") ? 1 : 0;
				var r = element.Value.Contains("R") ? 1 : 0;
				var u = element.Value.Contains("U") ? 1 : 0;
				var d = element.Value.Contains("D") ? 1 : 0;
				
				if(c+r+u+d == 0)
					continue;

				rights.Add(new ColumnRight()
				{
					RoleName = rolename,
					TableName = tableName,
					ColumnName = row.Title.Text,
					C = c,
					R = r,
					U = u,
					D = d
				});
			}
		}

		foreach (var groupedrights in rights.GroupBy(t => string.Join(string.Empty, t.RoleName, t.TableName, t.R, t.C, t.U, t.D)).OrderBy(t => t.Key))
		{
			var columnrights = groupedrights.ToList();
			var right = columnrights[0];

			sb.AppendFormat(@"INSERT tblUFGColumnRights (ucr_RoleName,ucr_TableName,ucr_ColumnName,ucr_SelectRight,ucr_SearchRight,ucr_InsertRight,ucr_UpdateRight,ucr_DeleteRight,ucr_VisibleInGrid)
SELECT '{0}',ucl_Tablename,ucl_ColumnName,{2},{3},{4},{5},{6},{7} FROM tblUFGColumns
WHERE ucl_TableName = '{1}'
AND ucl_ColumnName IN ({8})",
				right.RoleName, right.TableName, right.R, right.R, right.C, right.U, right.D, right.R, string.Join(",", columnrights.Select(r => "'" + r.ColumnName + "'").ToArray()));
			sb.AppendLine();
			sb.AppendLine();
		}
		
		sb.AppendLine("COMMIT TRAN");
		
		File.WriteAllText(GetConfig("output"), sb.ToString());
    }
	
	private static string GetConfig(string configname)
	{
		if(!File.Exists("config.xml"))
		{
			Console.WriteLine("config.xml geschreven, vul gegevens aan!");
			File.WriteAllText("config.xml", @"<?xml version=""1.0""?>
<config>
	<gmailuser>USER@gmail.com</gmailuser>
	<gmailpass>PASS</gmailpass>
	<output>303_CrudMatrix.sql</output>
</config>");
			Environment.Exit(1);
		}
	
		var doc = new XmlDocument();
		doc.Load("config.xml");
		return doc.SelectSingleNode("/config/" + configname).InnerXml;
	}

	private static string GetRoleName(string p)
	{
		switch (p)
		{
			case "ledenadministratieclub":
				return "Ledenadministratie Club";
			case "scheidsrechterscommissieclub":
				return "Scheidsrechterscommissie Club";
			case "technischecommissieclub":
				return "Technische commissie Club";
			case "voorzitterclub":
				return "Voorzitter Club";
			case "wedstrijdsecretariaatclub":
				return "Wedstrijdsecretariaat Club";
			case "ledenadministratiebond":
				return "Ledenadministratie Bond";
			case "scheidsrechterscommissiebond":
				return "Scheidsrechterscommissie Bond";
			case "tuchtcommissiebond":
				return "Tuchtcommissie Bond";
			case "wedstrijdsecretariaatbond":
				return "Wedstrijdsecretariaat Bond";
			case "leden":
				return "Leden";
		}
		return null;
	}
}

class ColumnRight
{
	public string RoleName { get; set; }
	public string TableName { get; set; }
	public string ColumnName { get; set; }

	public int C { get; set; }
	public int R { get; set; }
	public int U { get; set; }
	public int D { get; set; }
}

